package org.kjtc.exportService;

import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;

import javax.servlet.ServletOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.kjtc.balanceEntity.ChargingStation;

@SuppressWarnings({ "deprecation", "unused" })
public class ExportSummaryBalance<T> {

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public void exportExcel(String title, String[] headers, Collection<T> dataset, ServletOutputStream out, String pattern) throws Exception { 
		// 声明一个工作薄  
        HSSFWorkbook workbook = new HSSFWorkbook();  
        // 生成一个表格  
        HSSFSheet sheet = workbook.createSheet(title);
        // 纸张大小、方向
        HSSFPrintSetup ps = sheet.getPrintSetup();    
        ps.setLandscape(true); // 打印方向，true：横向，false：纵向(默认)     
        ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); //纸张类型    
        
        sheet.setColumnWidth(0, 1400);
        sheet.setColumnWidth(1, 3600);
        sheet.setColumnWidth(2, 3600);
        sheet.setColumnWidth(3, 3600);
        sheet.setColumnWidth(4, 3600);
        sheet.setColumnWidth(5, 3600);
        sheet.setColumnWidth(6, 3600);
        sheet.setColumnWidth(7, 3600);
        sheet.setColumnWidth(8, 3600);
        sheet.setColumnWidth(9, 3600);
        sheet.setColumnWidth(9, 3600);
        // 生成一个样式  
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式  
        style.setFillForegroundColor(HSSFColor.WHITE.index);  
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置自动换行
        style.setWrapText(true);
        // 设置文字水平居中
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置文字垂直居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成一个字体  
        HSSFFont font = workbook.createFont();  
        font.setColor(HSSFColor.AUTOMATIC.index);
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);
        // 加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式  
        style.setFont(font);
        
        // 生成一个样式  
        HSSFCellStyle style_data = workbook.createCellStyle();
        // 设置这些样式  
        style_data.setFillForegroundColor(HSSFColor.WHITE.index);  
        style_data.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style_data.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style_data.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style_data.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style_data.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置自动换行
        style_data.setWrapText(true);
        // 设置文字水平居中
        style_data.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置文字垂直居中
        style_data.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成一个字体  
        HSSFFont font_data = workbook.createFont();  
        font_data.setColor(HSSFColor.AUTOMATIC.index);
        font_data.setFontName("宋体");
        font_data.setFontHeightInPoints((short) 11);
        // 不加粗
        font_data.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式  
        style_data.setFont(font_data);
        
        HSSFCellStyle cellStyle1 = workbook.createCellStyle();  
        cellStyle1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        // 设置这些样式  
        cellStyle1.setFillForegroundColor(HSSFColor.WHITE.index);  
        cellStyle1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        cellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        cellStyle1.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        cellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        cellStyle1.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置自动换行
        cellStyle1.setWrapText(true);
        // 设置文字水平居中
        cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置文字垂直居中
        cellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        
        // 生成一个样式  
        HSSFCellStyle style_title = workbook.createCellStyle();
        // 设置这些样式  
        style_title.setFillForegroundColor(HSSFColor.WHITE.index);  
        style_title.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
//        style_title.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
//        style_title.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
//        style_title.setBorderRight(HSSFCellStyle.BORDER_THIN);  
//        style_title.setBorderTop(HSSFCellStyle.BORDER_THIN);  
        style_title.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style_title.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成一个字体  
        HSSFFont font_title = workbook.createFont();  
        font_title.setColor(HSSFColor.AUTOMATIC.index);  
        font_title.setFontHeightInPoints((short) 24);
        font_title.setFontName("宋体");
        // 加粗
        font_title.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式  
        style_title.setFont(font_title);
        
        // 产生title  
        HSSFRow row_title = sheet.createRow(0);
        row_title.setHeightInPoints(40);
        for (int i = 0; i < headers.length; i++) {
        	HSSFCell cell = row_title.createCell(i);  
            cell.setCellStyle(style_title);
		}
        Cell cell_title = row_title.createCell(0);
        cell_title.setCellStyle(style_title);
        cell_title.setCellValue(title);
        // 合并单元格
        CellRangeAddress cra_title = new CellRangeAddress(0, 0, 0, headers.length - 1);
        //在sheet里增加合并单元格  
        sheet.addMergedRegion(cra_title);
        
        // 产生标题行
        HSSFRow row_header = sheet.createRow(1);
        for (short i = 0; i < headers.length; i++) { 
            HSSFCell cell = row_header.createCell(i);  
            cell.setCellStyle(style);  
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);  
            cell.setCellValue(text);
           /* if (headers[i].contains("\r\n")) {
            	sheet.setColumnWidth(i, headers[i].getBytes().length*256);
			} else {
	            sheet.setColumnWidth(i, headers[i].getBytes().length*2*256);
			}*/
        }
        
        // 遍历集合数据，产生数据行  
        Iterator<T> it = dataset.iterator();  
        int index = 1;
        while (it.hasNext()){
        	index++;
        	HSSFRow row = sheet.createRow(index);
        	HSSFCell cell_No = row.createCell(0);
        	cell_No.setCellStyle(style_data);
        	cell_No.setCellValue(index - 1);
        	T t = (T) it.next();
        	// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值  
            Field[] fields = t.getClass().getDeclaredFields();
            for (int i = 0; i < fields.length; i++) {
                Field field = fields[i];  
                String fieldName = field.getName();  
                String getMethodName = "get"  + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                if ("getStartChargingDTTM".equals(getMethodName) || "getEndChargingDTTM".equals(getMethodName)) {
					break;
				} else if ("getChargingStationName".equals(getMethodName)) {
	            	HSSFCell cell = row.createCell(i + 1);
	            	cell.setCellStyle(style_data);  
					Class tCls = t.getClass();  
	                Method getMethod = tCls.getMethod(getMethodName, new Class[]{});  
	                String chargingStationNames = getMethod.invoke(t, new Object[]{}).toString();
	                String stationNames = chargingStationNames.replaceAll(",", "\r\n");
	                cell.setCellValue(stationNames);
				} else {
	            	HSSFCell cell = row.createCell(i + 1);
	            	cell.setCellStyle(style_data);  
					Class tCls = t.getClass();  
	                Method getMethod = tCls.getMethod(getMethodName, new Class[]{});  
	                Object value = getMethod.invoke(t, new Object[]{});
	                // 单元格赋值
	                setValue(value, cell, workbook, cellStyle1);
				}
			}
        }
        int length = dataset.size();
        sheet.addMergedRegion(new CellRangeAddress(2, length + 1, 1, 1));
        HSSFRow row_total = sheet.createRow(length + 2);
        row_total.setHeightInPoints(20);
        HSSFCell cell0 = row_total.createCell(0);
        cell0.setCellStyle(style_data);
        cell0.setCellValue(length + 1);
        HSSFCell cell1 = row_total.createCell(1);
        cell1.setCellStyle(style_data);
        cell1.setCellValue("合计");
        HSSFCell cell2 = row_total.createCell(2);
        cell2.setCellStyle(style_data);
        cell2.setCellValue("--");
        HSSFCell cell3 = row_total.createCell(3);
        cell3.setCellStyle(style_data);
        cell3.setCellValue("--");
        HSSFCell cell4 = row_total.createCell(4);
        cell4.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        cell4.setCellStyle(style_data);
        cell4.setCellFormula("SUM(E3:E" + String.valueOf(length + 2) + ")");
        HSSFCell cell5 = row_total.createCell(5);
        cell5.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        cell5.setCellStyle(style_data);
        cell5.setCellFormula("SUM(F3:F" + String.valueOf(length + 2) + ")");
        
		HSSFCellStyle cellStyle = workbook.createCellStyle();  
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        // 设置这些样式  
        cellStyle.setFillForegroundColor(HSSFColor.AUTOMATIC.index);  
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置自动换行
        cellStyle.setWrapText(true);
        // 设置文字水平居中
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置文字垂直居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        
        HSSFCell cell6 = row_total.createCell(6);
        cell6.setCellStyle(cellStyle);
        cell6.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        cell6.setCellFormula("SUM(G3:G" + String.valueOf(length + 2) + ")");
        HSSFCell cell7 = row_total.createCell(7);
        cell7.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        cell7.setCellStyle(cellStyle);
        cell7.setCellFormula("SUM(H3:H" + String.valueOf(length + 2) + ")");
        HSSFCell cell8 = row_total.createCell(8);
        cell8.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        cell8.setCellStyle(cellStyle);
        cell8.setCellFormula("SUM(I3:I" + String.valueOf(length + 2) + ")");
        HSSFCell cell9 = row_total.createCell(9);
        cell9.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        cell9.setCellStyle(cellStyle);
        cell9.setCellFormula("SUM(J3:J" + String.valueOf(length + 2) + ")");
        workbook.write(out);
        out.flush();
        out.close();
    }

	private void setValue(Object value, HSSFCell cell, HSSFWorkbook workbook, HSSFCellStyle cellStyle) {
		if (value instanceof Integer) {
			int intValue = (Integer) value;
			cell.setCellValue(intValue);
		} else if (value instanceof Double) {
			double dValue = (Double) value;
			cell.setCellValue(dValue);
			
            cell.setCellStyle(cellStyle);
		} else if (value instanceof String) {
			String strValue = value.toString();
			cell.setCellValue(strValue);
		}
	}
}
